package cdms.db.jdbc;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;


import cdms.db.DeliveryOrderRepository;
import cdms.domain.DeliveryOrder;
import cdms.domain.Manager;
import cdms.domain.Sender;
import cdms.web.PaginationSupport;

/**
 * 
 * 
 * <p>
 * Title: JdbcDeliveryOrderRepository
 * </p>
 * 
 * <p>
 * Description:
 * </p>
 * 
 * @author zhangxuhao JiHongru
 * 
 * @date Nov 17, 2019
 */
@Repository
public class JdbcDeliveryOrderRepository implements DeliveryOrderRepository {

	@Autowired
	private JdbcTemplate jdbc;

	/*
	 * (non-Javadoc)
	 * 
	 * <p>Title: count</p>
	 * 
	 * <p>Description: </p>
	 * 
	 * @return
	 * 
	 * @see cdms.db.DeliveryOrderRepository#count()
	 * 
	 */
	@Override
	public long count() {

		return jdbc.queryForLong("select count(id) from deliveryOrder");

	}

	/**
	 * get the count of search in all
	 * @param type
	 * @param search
	 * @return
	 */
	public long searchCount(String type, String search) {
		long count = 0;
		if (type.equals("orderId")) {
			count = jdbc.queryForLong("select count(id) from deliveryOrder where orderId like ? ", search);
		} else if (type.equals("senderUserName")) {
			count = jdbc.queryForLong(
					"select count(d.id) from deliveryorder d join manager ma on d.managerid = ma.id left join sender s on s.id = d.senderid where s.userName like ? ",
					search);
		} else if (type.equals("managerUserName")) {
			count = jdbc.queryForLong(
					"select count(d.id) from deliveryorder d join manager ma on d.managerid = ma.id left join sender s on s.id = d.senderid where ma.userName like ? ",
					search);
		}
		return count;
	}

	/**
	 * get the count of state
	 * @param state
	 * @return
	 */
	public long stateCount(int state) {
		return jdbc.queryForLong("select count(id) from deliveryOrder where state=? ", state);
	}

	/**
	 * get the count of search in state
	 * @param type
	 * @param search
	 * @param state
	 * @return
	 */
	public long searchStateCount(String type, String search, int state) {
		long count = 0;
		if (type.equals("orderId")) {
			count = jdbc.queryForLong("select count(id) from deliveryOrder where orderId like ? and state=? ", search,
					state);
		} else if (type.equals("senderUserName")) {
			count = jdbc.queryForLong(
					"select count(d.id) from deliveryorder d join manager ma on d.managerid = ma.id left join sender s on s.id = d.senderid where s.userName like ? and state=? ",
					search, state);
		} else if (type.equals("managerUserName")) {
			count = jdbc.queryForLong(
					"select count(d.id) from deliveryorder d join manager ma on d.managerid = ma.id left join sender s on s.id = d.senderid where ma.userName like ? and state=? ",
					search, state);
		}
		return count;
	}

	/**
	 * get the count of order by senderId
	 * @param senderId
	 * @return long
	 */
	public long count(Long senderId) {

		return jdbc.queryForLong("select count(id) from deliveryorder where senderid = " + senderId);

	}

	/**
	 * get count of order not received
	 * 
	 * @return long
	 *
	 */
	public long countWei() {

		return jdbc.queryForLong("select count(id) from deliveryorder where senderid is null");

	}

	/*
	 * (non-Javadoc)
	 * 
	 * <p>Title: save</p>
	 * 
	 * <p>Description: </p>
	 * 
	 * @param deliveryOrder
	 * 
	 * @return
	 * 
	 * @see cdms.db.DeliveryOrderRepository#save(cdms.domain.DeliveryOrder)
	 * 
	 */
	@Override
	public DeliveryOrder save(DeliveryOrder deliveryOrder) {
		jdbc.update(INSERT_ORDER, deliveryOrder.getOrderId(), deliveryOrder.getManager().getId(),
				deliveryOrder.getReceiveAddress(), deliveryOrder.getReceivePhone(), deliveryOrder.getDeliverAddress(),
				deliveryOrder.getDeliverPhone(), deliveryOrder.getReleaseTime(), deliveryOrder.getCost(),
				deliveryOrder.getState());
		return deliveryOrder;
	}

	/**
	 * update order information
	 * 
	 * @param order
	 * @return DeliveryOrder
	 */
	public DeliveryOrder update(DeliveryOrder order) {
		jdbc.update(UPDATE_ORDER, order.getSender().getId(), order.getReceiveAddress(), order.getReceivePhone(),
				order.getDeliverAddress(), order.getDeliverPhone(), order.getReceiveTime(), order.getFinishTime(),
				order.getCost(), order.getState(), order.getOrderId());
		return order;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * <p>Title: findOne</p>
	 * 
	 * <p>Description: </p>
	 * 
	 * @param id
	 * 
	 * @return
	 * 
	 * @see cdms.db.DeliveryOrderRepository#findOne(java.lang.Long)
	 * 
	 */
	@Override
	public DeliveryOrder findOne(Long id) {
		DeliveryOrder deliveryOrder = null;
		try {
			deliveryOrder = jdbc.queryForObject(SELECT_ORDER + " and id=?", new DeliveryOrderRowMapper(), id);
		} catch (DataAccessException e) {
		}
		return deliveryOrder;
	}

	/**
	 * find a order by sender Id
	 * 
	 * @param senderId
	 * @return DeliveryOrder
	 */
	public DeliveryOrder findOneBySenderId(Long senderId) {
		DeliveryOrder deliveryOrder = null;
		try {
			deliveryOrder = jdbc.queryForObject(SELECT_ORDER + " where state = 1 and senderid = ?",
					new DeliveryOrderRowMapper(), senderId);
		} catch (DataAccessException e) {
		}
		return deliveryOrder;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * <p>Title: findByOrderId</p>
	 * 
	 * <p>Description: </p>
	 * 
	 * @param orderId
	 * 
	 * @return
	 * 
	 * @see cdms.db.DeliveryOrderRepository#findByOrderId(java.lang.Long)
	 * 
	 */
	@Override
	public DeliveryOrder findByOrderId(String orderId) {
		DeliveryOrder deliveryOrder = null;
		try {
			deliveryOrder = jdbc.queryForObject(SELECT_ORDER + " where orderid = ?", new DeliveryOrderRowMapper(),
					orderId);
		} catch (DataAccessException e) {
		}
		return deliveryOrder;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * <p>Title: findAll</p>
	 * 
	 * <p>Description: </p>
	 * 
	 * @return
	 * 
	 * @see cdms.db.DeliveryOrderRepository#findAll()
	 * 
	 */
	@Override
	public List<DeliveryOrder> findAll() {
		return jdbc.query(SELECT_ORDER + " order by id", new DeliveryOrderRowMapper());

	}

	/*
	 * (non-Javadoc)
	 * 
	 * <p>Title: findPage</p>
	 * 
	 * <p>Description: </p>
	 * 
	 * @param pageNo
	 * 
	 * @param pageSize
	 * 
	 * @return
	 * 
	 * @see cdms.db.DeliveryOrderRepository#findPage(int, int)
	 * 
	 */
	@Override
	public PaginationSupport<DeliveryOrder> findPage(int pageNo, int pageSize, Long senderId, boolean flag) {
		if (flag) {
			int totalCount = (int) count(senderId);
			int startIndex = PaginationSupport.convertFromPageToStartIndex(pageNo, pageSize);
			if (totalCount < 1) {
				return new PaginationSupport<DeliveryOrder>(new ArrayList<DeliveryOrder>(0), 0);
			}
			List<DeliveryOrder> orders = jdbc.query(SELECT_ORDER + " where senderid = ?" + SELECT_PAGE_ORDER,
					new DeliveryOrderRowMapper(), senderId, pageSize, startIndex);
			PaginationSupport<DeliveryOrder> ps = new PaginationSupport<DeliveryOrder>(orders, totalCount, pageSize, startIndex);
			return ps;
		} else {
			int totalCount = (int) countWei();
			int startIndex = PaginationSupport.convertFromPageToStartIndex(pageNo, pageSize);
			if (totalCount < 1) {
				return new PaginationSupport<DeliveryOrder>(new ArrayList<DeliveryOrder>(0), 0);
			}
			List<DeliveryOrder> orders = jdbc.query(SELECT_ORDER + " where senderid is null" + SELECT_PAGE_ORDER,
					new DeliveryOrderRowMapper(), pageSize, startIndex);
			PaginationSupport<DeliveryOrder> ps = new PaginationSupport<DeliveryOrder>(orders, totalCount, pageSize, startIndex);
			return ps;
		}

	}

	/**
	 * @param pageNo
	 * @param pageSize
	 * @return PaginationSupport<DeliveryOrder> 该方法为管理员提供订单记录的分页类
	 * 
	 */
	public PaginationSupport<DeliveryOrder> findPage(int pageNo, int pageSize) {
		int totalCount = (int) count();
		int startIndex = PaginationSupport.convertFromPageToStartIndex(pageNo, pageSize);
		if (totalCount < 1) {
			return new PaginationSupport<DeliveryOrder>(new ArrayList<DeliveryOrder>(0), 0);
		}
		List<DeliveryOrder> orders = jdbc.query(SELECT_ORDER + SELECT_PAGE_ORDER, new DeliveryOrderRowMapper(),
				pageSize, startIndex);
		PaginationSupport<DeliveryOrder> ps = new PaginationSupport<DeliveryOrder>(orders, totalCount, pageSize,
				startIndex);
		return ps;
	}

	/**
	 * get the orders of search in all and show in page
	 * @param pageNo
	 * @param pageSize
	 * @param type
	 * @param search
	 * @return
	 */
	public PaginationSupport<DeliveryOrder> searchAllPage(int pageNo, int pageSize, String type, String search) {
		search = "%" + search + "%";
		int totalCount = (int) searchCount(type, search);
		int startIndex = PaginationSupport.convertFromPageToStartIndex(pageNo, pageSize);
		if (totalCount < 1) {
			return new PaginationSupport<DeliveryOrder>(new ArrayList<DeliveryOrder>(0), 0);
		}
		List<DeliveryOrder> orders = null;
		if (type.equals("orderId")) {
			orders = jdbc.query(SEARCH_ALL_ORDERID + SELECT_PAGE_ORDER, new DeliveryOrderRowMapper(), search, pageSize,
					startIndex);
		} else if (type.equals("senderUserName")) {
			orders = jdbc.query(SEARCH_ALL_SENDERUSERNAME + SELECT_PAGE_ORDER, new DeliveryOrderRowMapper(), search, pageSize,
					startIndex);
		} else if (type.equals("managerUserName")) {
			orders = jdbc.query(SEARCH_ALL_MANAGERUSERNAME + SELECT_PAGE_ORDER, new DeliveryOrderRowMapper(), search,
					pageSize, startIndex);
		}
		PaginationSupport<DeliveryOrder> ps = new PaginationSupport<DeliveryOrder>(orders, totalCount, pageSize,
				startIndex);
		return ps;
	}

	/**
	 *  get the orders of state and show in page
	 * @param pageNo
	 * @param pageSize
	 * @param type
	 * @param search
	 * @return
	 */
	public PaginationSupport<DeliveryOrder> statePage(int pageNo, int pageSize, int state) {
		int totalCount = (int) stateCount(state);
		int startIndex = PaginationSupport.convertFromPageToStartIndex(pageNo, pageSize);
		if (totalCount < 1) {
			return new PaginationSupport<DeliveryOrder>(new ArrayList<DeliveryOrder>(0), 0);
		}
		List<DeliveryOrder> orders = jdbc.query(SELECT_BY_STATE + SELECT_PAGE_ORDER, new DeliveryOrderRowMapper(),
				state, pageSize, startIndex);
		PaginationSupport<DeliveryOrder> ps = new PaginationSupport<DeliveryOrder>(orders, totalCount, pageSize,
				startIndex);
		return ps;
	}

	/**
	 * get the orders of search in state and show in page
	 * @param pageNo
	 * @param pageSize
	 * @param type
	 * @param search
	 * @return
	 */
	public PaginationSupport<DeliveryOrder> searchStatePage(int pageNo, int pageSize, String type, String search,
			int state) {
		search = "%" + search + "%";
		int totalCount = (int) searchStateCount(type, search, state);
		int startIndex = PaginationSupport.convertFromPageToStartIndex(pageNo, pageSize);
		if (totalCount < 1) {
			return new PaginationSupport<DeliveryOrder>(new ArrayList<DeliveryOrder>(0), 0);
		}
		List<DeliveryOrder> orders = null;
		if (type.equals("orderId")) {
			orders = jdbc.query(SEARCH_BY_STATE_ORDERID + SELECT_PAGE_ORDER, new DeliveryOrderRowMapper(), search,
					state, pageSize, startIndex);
		} else if (type.equals("senderUserName")) {
			orders = jdbc.query(SEARCH_BY_STATE_SENDERUSERNAME + SELECT_PAGE_ORDER, new DeliveryOrderRowMapper(), search,
					state, pageSize, startIndex);
		} else if (type.equals("managerUserName")) {
			orders = jdbc.query(SEARCH_BY_STATE_MANAGERUSERNAME + SELECT_PAGE_ORDER, new DeliveryOrderRowMapper(), search,
					state, pageSize, startIndex);
		}
		PaginationSupport<DeliveryOrder> ps = new PaginationSupport<DeliveryOrder>(orders, totalCount, pageSize,
				startIndex);
		return ps;
	}

	/**
	 * 
	 * <p>
	 * Class name:DeliveryOrderRowMapper
	 * </p>
	 * 
	 * @version
	 * @date 2019年11月20日下午5:37:15
	 *
	 */
	private static class DeliveryOrderRowMapper implements RowMapper<DeliveryOrder> {
		public DeliveryOrder mapRow(ResultSet rs, int rowNum) throws SQLException {
			Manager manager = new Manager(rs.getLong("managerid"), rs.getString("musername"), rs.getString("mpassword"),
					rs.getString("mfullname"), rs.getString("email"), rs.getString("mphoneno"),
					rs.getBoolean("misdeleted"));
			Sender sender = null;
			if (rs.getString("senderid") != null) {
				sender = new Sender(rs.getLong("senderid"), rs.getString("susername"), rs.getString("spassword"),
						rs.getString("sfullname"), rs.getBoolean("sisdeleted"), rs.getString("sphoneno"), false);
			}
			return new DeliveryOrder(rs.getLong("id"), rs.getString("orderId"), manager, sender,
					rs.getString("receiveaddress"), rs.getString("receivephone"), rs.getString("deliveraddress"),
					rs.getString("deliverphone"), rs.getDouble("cost"), rs.getTimestamp("releasetime"),
					rs.getTimestamp("receivetime"), rs.getTimestamp("finishtime"), rs.getInt("state"));
		}
	}

	/**
	 * PostgreSql insert delivery orders phrases
	 */
	private static final String INSERT_ORDER = "insert into deliveryorder (orderid,managerid,receiveaddress,receivephone,deliveraddress,deliverphone,releasetime,cost,state) values (?, ?, ?, ?, ?,?,?,?,?)";

	/**
	 * PostgreSql update delivery orders phrases
	 */
	private static final String UPDATE_ORDER = "update deliveryorder set senderid = ?,receiveaddress = ?,receivephone = ?,deliveraddress = ?,deliverphone = ?,receivetime = ?,finishtime = ?,cost = ?,state = ? where orderid = ?";

	/**
	 * PostgreSql select delivery orders phrases
	 */
	private static final String SELECT_ORDER = "select d.id,d.orderid,managerid,senderid,d.receiveaddress,d.receivephone,d.deliveraddress,d.deliverphone,d.releasetime,d.receivetime,d.finishtime,d.cost,d.state,\n"
			+ "	s.username as susername,s.password as spassword,s.fullname as sfullname,s.isdeleted as sisdeleted,s.phoneno as sphoneno,ma.username as musername,ma.password as mpassword,"
			+ "ma.phoneno as mphoneno,ma.email,ma.isdeleted as misdeleted,ma.fullname as mfullname\n"
			+ "	from deliveryorder d join manager ma on d.managerid = ma.id left join sender s on s.id = d.senderid";

	/**
	 * PostgreSql select page of delivery orders phrases
	 */
	private static final String SELECT_PAGE_ORDER = " order by receivetime desc limit ? offset ?";

	private static final String SELECT_BY_STATE = SELECT_ORDER + " where state = ? ";

	private static final String SEARCH_ALL_ORDERID = SELECT_ORDER + " where orderId like ? ";

	private static final String SEARCH_ALL_SENDERUSERNAME = SELECT_ORDER + " where s.userName like ? ";

	private static final String SEARCH_ALL_MANAGERUSERNAME = SELECT_ORDER + " where ma.userName like ? ";

	private static final String SEARCH_BY_STATE_ORDERID = SEARCH_ALL_ORDERID + " and state = ? ";

	private static final String SEARCH_BY_STATE_SENDERUSERNAME = SEARCH_ALL_SENDERUSERNAME + " and state = ? ";

	private static final String SEARCH_BY_STATE_MANAGERUSERNAME = SEARCH_ALL_MANAGERUSERNAME + " and state = ? ";
}
